<?php
// +----------------------------------------------------------------------
// | Description: 用户
// +----------------------------------------------------------------------
// | Author: linchuangbin <linchuangbin@honraytech.com>
// +----------------------------------------------------------------------

namespace app\admin\model;

use think\Db;
use app\admin\model\Common;
use com\verify\HonrayVerify;
use PHPExcel;
use app\admin\model\Myloans;
class Operating extends Common
{

 
    protected $name = 'admin_user_clien';
  
    /**
     * [getDataList 列表]
     * @AuthorHTL
     * @DateTime  2017-02-10T22:19:57+0800
     * @param     [string]                   $keywords [关键字]
     * @param     [number]                   $page     [当前页数]
     * @param     [number]                   $limit    [t每页数量]
     * @return    [array]                             [description]
     */
    public function getDataList($keywords='', $page, $limit, $valuedate,$valuedateend)
    {
        $map = [];

        if($valuedateend&&$valuedate)
        {
            $valuedate = substr($valuedate,0,10);
            $valuedate = date('Y-m-d',strtotime("$valuedate +1 day"));
            $valuedateend = substr($valuedateend,0,10);
            $valuedateend = date('Y-m-d',strtotime("$valuedateend +1 day"));
        }
        elseif($valuedate) {
            $valuedate = substr($valuedate,0,10);
            $valuedate = date('Y-m-d',strtotime("$valuedate +1 day"));
        }
        elseif($valuedateend) {
            $valuedateend = substr($valuedateend,0,10);
            $valuedate = date('Y-m-d',strtotime("$valuedateend +1 day"));
        }

        $list = Db::name('admin_group')->where('id','>=',21)->field('title,id')->select();
        foreach($list as $key=>$value)
        {
            $where['group_id'] = $value['id'];
            $user_id = Db::name('admin_access')->alias('a')->join('qh_admin_user b','b.id = a.user_id')
                       ->field('b.username,b.id')->where($where)->select();
            $list[$key]['username'] = $user_id;    //用户

            foreach($list[$key]['username'] as $key1=>$value)
            {
                $maprl['admin_id'] = $value['id'];
                $maprlike['nowdate'] = ['like', '%'.$valuedate.'%'];
                if($valuedateend&&$valuedate)
                {
                   $list[$key]['username'][$key1]['seqcount'] = Db::name('claim_seq')->where($maprl)
                                                               ->where('nowdate','>=',$valuedate)
                                                               ->where('nowdate','<',$valuedateend)
                                                               ->count();   //认领情况
                }else{
                    $list[$key]['username'][$key1]['seqcount'] = Db::name('claim_seq')->where($maprl)->where($maprlike)->count();   //认领情况
                }
                
            }

            foreach($list[$key]['username'] as $key2=>$value)
            {
                $map1['admin_user_id'] = $value['id'];
                $map1['revoked'] = 1;
                $mapx['status'] = 0;  //驳回
                $mapt['status'] = 1;  //通过
                $mapy['status'] = 2;  //移交
                $mapq['status'] = 3;  //确认还款
                $maplike['date'] = ['like', '%'.$valuedate.'%'];

                if($valuedateend&&$valuedate)
                {

                    $list[$key]['username'][$key2]['claimcount'] = Db::name('admin_user_clien')->where($map1)
                                                                   ->where('date','>=',$valuedate)
                                                                   ->where('date','<',$valuedateend)
                                                                   ->count(); //处理情况
                    $list[$key]['username'][$key2]['claimcountx'] = Db::name('admin_user_clien')->where($map1)->where($mapx)
                                                                   ->where('date','>=',$valuedate)
                                                                   ->where('date','<',$valuedateend)
                                                                   ->count(); //驳回
                    $list[$key]['username'][$key2]['claimcountt'] = Db::name('admin_user_clien')->where($map1)->where($mapt)
                                                                   ->where('date','>=',$valuedate)
                                                                   ->where('date','<',$valuedateend)
                                                                   ->count(); //通过
                    $list[$key]['username'][$key2]['claimcounty'] = Db::name('admin_user_clien')->where($map1)->where($mapy)
                                                                   ->where('date','>=',$valuedate)
                                                                   ->where('date','<',$valuedateend)
                                                                   ->count(); //通过->count(); //移交
                    $list[$key]['username'][$key2]['claimcountq'] = Db::name('admin_user_clien')->where($map1)->where($mapq)
                                                                   ->where('date','>=',$valuedate)
                                                                   ->where('date','<',$valuedateend)
                                                                   ->count(); //通过->count(); //确认还款
                }else{
                    $list[$key]['username'][$key2]['claimcount'] = Db::name('admin_user_clien')->where($map1)->where($maplike)->count(); //处理情况
                    $list[$key]['username'][$key2]['claimcountx'] = Db::name('admin_user_clien')->where($map1)->where($maplike)->where($mapx)->count(); //驳回
                    $list[$key]['username'][$key2]['claimcountt'] = Db::name('admin_user_clien')->where($map1)->where($maplike)->where($mapt)->count(); //通过
                    $list[$key]['username'][$key2]['claimcounty'] = Db::name('admin_user_clien')->where($map1)->where($maplike)->where($mapy)->count(); //移交
                    $list[$key]['username'][$key2]['claimcountq'] = Db::name('admin_user_clien')->where($map1)->where($maplike)->where($mapq)->count(); //确认还款
                }
                
            }
        }
        $data['list'] = $list;
        $data['dataCount'] = $dataCount;
        return $data;
    }



   
   /**
    * 功能:     员工审核统计
    * @Author   zhanhuiping
    * @email    18702529695@163.com
    * @DateTime 2017-09-23
    * @param    string              $keywords     [description]
    * @param    [type]              $page         [description]
    * @param    [type]              $limit        [description]
    * @param    [type]              $valuedate    [description]
    * @param    [type]              $valuedateend [description]
    * @return   [type]                            [description]
    */
   public function getTongji($keywords='', $page, $limit, $valuedate,$valuedateend)
   {
        

        $valuedateend = $valuedate[1];
        $valuedate    = $valuedate[0];
        
        // p()
      #默认当天0 点开始 
       if(!$valuedate){
            $valuedate = date("Y-m-d",time());
            $valuedate = $valuedate." 00:00:00";
        }else{
            $valuedate = date('Y-m-d H:i:s',strtotime($valuedate));
        }
        #默认 截止到当前时间为止
        if(!$valuedateend){
            $valuedateend = date("Y-m-d H:i:s",time());
        }else{
            $valuedateend = date('Y-m-d H:i:s',strtotime($valuedateend));
        }
        
       
      $date_time = array('BETWEEN',[$valuedate,$valuedateend]);
      
      $where ='time|check_time|check2_time|merchandiser2_time|fk_time|first_claim_date|lendmoneydate|
                second_claim_date|third_claim_date|fourth_claim_date|myloan_claim_date|monepassdate' ;
      $map[$where]        = $date_time;
      $map_dx['ele_time'] = $date_time;
      $field = 'id,uid,status,time,check_aid,check_time,check2_aid,check2_time,merchandiser,monepassdate,merchandiser2,merchandiser2_time,
               treasurer,fk_time,hk_time,appoint_time,money,actual_money,loan_out_money,transfer,first_claim_date,second_claim_date,third_claim_date,fourth_claim_date,renewal_count,myloan_claim_date,lendmoneydate';
      $info =  Db::name('apply')->field($field)->where('id > 0')->where($map)->select();
      
      
//     var_dump($info);exit;
      $info_dx = Db::name('user_info')->field('ele_time,ele_status,ele_aid')->where($map_dx)->select();

      $user_list = Db::name('admin_group_user')->field('group_id,user_id,username,title')->where('group_id > 20')->select();
      
      $list = Db::name('admin_group_user')->field('group_id,title')->where('id > 0')->where('group_id > 20')->group('group_id')->select();
      foreach($list as $key=>$value)
      {
         $group_id = $value['group_id'];
         
        //单独处理电销                                                  
        if($group_id == 27)
        {
            //子查询
            $subQuery = Db::name('user_info')
                ->alias('a')
                ->field('b.auth_type,b.uid')
                ->join('apply b','a.uid = b.uid','left')
                ->where(['ele_time' => $date_time, 'a.ele_status' => 1])
                ->group('a.uid')
                ->order('b.id')
                ->buildSql();
            //查询认证统计信息
            $user_list = Db::name('admin_group_user')->alias('a')->field(['a.user_id, a.username',"SUM(IF(c.auth_type = 2, 1, 0)) vip"])
                ->join('user_info b','b.ele_aid = a.user_id','left')
                ->join($subQuery .' c','c.uid = b.uid','left')
                ->where(['group_id'=> 27])
                ->group('a.id')
                ->select();
        }else{
            $user_list = Db::name('admin_group_user')
                ->field('group_id,user_id,title,username')
                ->where('id > 0')
                ->where('group_id > 20')
                ->where(['group_id'=>$group_id])
                ->select();   
        }                                     
         $list[$key]['user'] =  $user_list;
         // print_r($info);
         foreach($list[$key]['user'] as $key1=>$value1)
          {
              switch ($group_id) {

                  case '21':
                        
                        # 用户id（公共模块）
                         $admin_user = 'check_aid';
                        
                        // 认领人数
                         $renltime = 'first_claim_date';
                      
                        // 总处理
                         $chulitime = 'check_time';
                        
                        // 驳回
                         $user_status_x = array(6);

                        // 通过
                         $user_status_p = array(1,2,3,4,5,7,9,10,11,12);

                        // 放款
                         $user_status_fk = array(3,4,5,12);
                         $fk_date = 'fk_time';

                        // 回款
                         $hk_date = 'hk_time';

                        // 移交
                         $move = 'transfer';
                         
                      break;

                case '22':
                
                      # 用户id（公共模块）
                         $admin_user = 'check2_aid';
                        
                        // 认领人数
                         $renltime = 'second_claim_date';
                      
                        // 总处理
                         $chulitime = 'check2_time';
                        
                        // 驳回
                         $user_status_x = array(7);

                        // 通过
                         $user_status_p = array(2,3,4,5,9,10,11,12);

                        // 放款
                         $user_status_fk = array(4,5,3,12);
                         $fk_date = 'fk_time';

                        // 回款
                         $hk_date = 'hk_time';

                         // 移交
                         $move = 'transfer';
                     
                    

                      break;
                case '23':
                       
                        # 用户id（公共模块）
                         $admin_user = 'merchandiser';
                        
                        // 认领人数
                         $renltime = 'third_claim_date';
                      
                        // 总处理
                         $chulitime = 'monepassdate';
                        
                        // 驳回
                         $user_status_x = array(10);

                        // 通过
                         $user_status_p = array(3,4,5,9,12);

                        // 放款
                         $user_status_fk = array(4,5,3,12);
                         $fk_date = 'fk_time';

                        // 回款
                         $hk_date = 'hk_time';
                         $user_status_hk = array(6);

                         // 移交
                         $move = 'transfer';
                        
                      


                      break;
                case '24':
              
                       # 用户id（公共模块）
                         $admin_user = 'treasurer';
                        
                        // 认领人数
                         $renltime = 'myloan_claim_date';
                      
                        // 总处理
                         $chulitime = 'fk_time';
                        
                        // 驳回
                         $user_status_x = array(11);

                        // 通过
                         $user_status_p = array(3,4,5,12);

                        // 放款
                         $user_status_fk = array(3,4,5,12);
                         $fk_date = 'fk_time';

                        // 回款
                         $hk_date = 'hk_time';
                         $user_status_hk = array(5);

                         // 移交
                         $move = 'transfer';
                     
                     
                      


                      break;
                case '25':
                         
                      
                        # 用户id（公共模块）
                         $admin_user = 'merchandiser2';
                        
                        // 认领人数
                         $renltime = 'fourth_claim_date';
                      
                        // 总处理
                         $chulitime = 'fk_date';
                        
                        // 驳回
                         $user_status_x = array(10);

                        // 通过
                         $user_status_p = array(4,5,6,11);

                        // 放款
                         $user_status_fk = array(4,5,6,11);
                         $fk_date = 'fk_time';

                        // 回款
                         $hk_date = 'hk_time';
                         $user_status_hk = array(6);

                         // 移交
                         $move = 'transfer2';
                      


                      break;
                case '26':
                       
                       
                        # 用户id（公共模块）
                         $admin_user = 'six_employee_id';
                        
                        // 认领人数
                         $renltime = 'six_claim_date';
                      
                        // // 总处理
                        //  $chulitime = 'fourth_date';
                        
                        // // 驳回
                        //  $user_status_x = array(10);

                        // // 通过
                        //  $user_status_p = array(4,5,6,11);

                        // // 放款
                        //  $user_status_fk = array(4,5,6,11);
                        //  $fk_date = 'fk_date';

                        // // 回款
                        //  $hk_date = 'hk_date';
                        //  $user_status_hk = array(6);

                      break;

                case '27':
                       $rl = 0;
                       $cl = 0;
                       $bh = 0;
                       $tg = 0;
                        # 用户id（公共模块）
                         foreach($info_dx as $key4=>$value4)
                         {
                            if($value4['ele_aid']==$value1['user_id'])
                            {
                                  if($value4['ele_time']>=$valuedate && $value4['ele_time']<$valuedateend)
                                  {

                                       $rl++;
                                      
                                       if($value4['ele_status']==2)
                                       {
                                          $bh++;
                                       }
                                       if($value4['ele_status']==1)
                                       {
                                          $tg++;
                                       }
                                  }
                            }
                         }
                        
                        $cl = $tg+$bh;

                        $biao = 1;
                         

                      break;
                  default:
                      # code...
                      break;
              }

           
           if(!$biao)
           {

             
              
                    $rl   = 0; // 认领
                    $cl   = 0; // 处理
                    $bh   = 0; // 驳回
                    $tg   = 0; // 通过
                    $fk   = 0; // 放款
                    $yq   = 0; // 延期
                    $hk   = 0; // 回款
                    $wdq  = 0; // 未到期
                    $ashk = 0; // 按时还款
                    $yj   = 0; // 移交


               $list[$key]['user'][$key1]['fk_amount'] =  0;  //放款金额
               $list[$key]['user'][$key1]['hk_amount'] =  0;  //回款金额
               $list[$key]['user'][$key1]['yj_amount'] =  0;  //移交金额
                    
                    # 认领
                    foreach($info as $key3=>$value3)
                    {

                       if($value3[$admin_user] == $value1['user_id'] )
                       {
                          if(($value3[$renltime]>=$valuedate) && ($value3[$renltime] <= $valuedateend))
                          {
                             $rl++;
                          }
                       }

                    }

                   
                   # 处理
                    foreach($info as $key3=>$value3)
                    {

                       if($value3[$admin_user] == $value1['user_id'] )
                       {
                          if(($value3[$chulitime]>=$valuedate) && ($value3[$chulitime] <= $valuedateend))
                          {
                             $cl++;
                             if(in_array($value3['status'], $user_status_x))
                             {
                                $bh++;
                             }
                             
                             if(in_array($value3['status'], $user_status_p))
                             {
                                $tg++;
                             }
                          }
                       }

                    }
                   



                   # 放款
                    foreach($info as $key3=>$value3)
                    {

                       if($value3[$admin_user] == $value1['user_id'] )
                       {
                          if((($value3[$fk_date]>=$valuedate) && ($value3[$fk_date] <= $valuedateend)) || (($value3['lendmoneydate']>=$valuedate) && ($value3['lendmoneydate'] <= $valuedateend)) )
                          {
                              $list[$key]['user'][$key1]['fk_amount'] +=$value3['money'];  //放款金额
                              $list[$key]['user'][$key1]['sj_amount'] +=$value3['loan_out_money'];  //实际金额
                              $fk++;
                              if($value3['status'] == 5)
                              {
                                  $list[$key]['user'][$key1]['hk_amount'] +=$value3['actual_money'];    //还款金额
                                $hk++;
                              }
                              if($value3[$move]==1)
                              {
                                  $list[$key]['user'][$key1]['yj_amount'] +=$value3['money'];//移交金额
                                $yj++;
                              } 

                              if($value3['status'] == 12)
                              {
                                 $yq++;
                              }

                              if($value3['status'] == 3)
                              {
                                $wdq++; 
                              }

                              if($value3['hk_time'] <= $value3['appoint_time'] && $value3['status'] == 5)
                              {
                                $ashk++;
                              }
                          }
                       }

                    }
             
             
                  # 还款(率)
                  if(($fk-$wdq)!=0){
                    $list[$key]['user'][$key1]['hk_rate'] = 
                    substr(($hk/($fk-$wdq))*100,0,5);
                  }else{
                    $list[$key]['user'][$key1]['hk_rate'] = 0;
                  }

                  # 部分还款率
                  if(($fk-$wdq)!=0){
                    $list[$key]['user'][$key1]['bf_rate'] = 
                    substr((($hk+$yq)/($fk-$wdq))*100,0,5);
                  }else{
                    $list[$key]['user'][$key1]['bf_rate'] = 0;
                  }
             
             }

             # 通过(率)
              if($cl!=0){
                $list[$key]['user'][$key1]['tg_rate'] = 
                substr(($tg/$cl)*100,0,5);
              }else{
                $list[$key]['user'][$key1]['tg_rate'] = 0;
              }

              $list[$key]['user'][$key1]['claim']                 = $rl;  // 认领
              $list[$key]['user'][$key1]['dealwith']              = $cl;  // 处理
              $list[$key]['user'][$key1]['turndown']              = $bh;  // 驳回
              $list[$key]['user'][$key1]['pass']                  = $tg;  // 通过
              $list[$key]['user'][$key1]['loan']                  = $fk;  // 放款
              $list[$key]['user'][$key1]['repayments']            = $hk;  // 回款
              $list[$key]['user'][$key1]['weidaoqi']              = $wdq; //未到期
              $list[$key]['user'][$key1]['finc_sure_yanqi_count'] = $yq;  // 延期
              $list[$key]['user'][$key1]['Repayment_anshi']       = $ashk;  // 按时还款
              $list[$key]['user'][$key1]['move']                  = $yj;  //移交
              


              

          }

           
          
      }
       $data['list'] = $list;

       return $data;
     // print_r($list);exit;
       
        // $list = Db::name('admin_group')->where('id','>=',21)->field('title,id')->select();

        // foreach($list as $key=>$value) {
        //     $map_cs=array();
        //     #获取 该分组下的所有组员
        //     $where['group_id'] = $value['id'];
        //     $user_id = Db::name('admin_access')->alias('a')
        //                                        ->join('qh_admin_user b','b.id = a.user_id')
        //                                        ->field('b.username,b.id')
        //                                        ->where($where)
        //                                        ->select();
        //     $list[$key]['username'] = $user_id;    

        //     foreach($list[$key]['username'] as $key1=>$value1) {

        //         switch ($where['group_id']) {
        //             case 21:
        //                 $map_cs['check_aid']   = $value1['id'];                         //初审认领
        //                 $apply_time_claim      = "first_claim_date";                    //初审认领时间

        //                 $mapx_status['status'] = 6;                                     //初审驳回
        //                 $mapt_status['status'] = array('in','1,2,3,4,5,7,9,10,11,12');  //初审通过
        //                 $opreat_time_key       = "check_time";                          //初审处理时间
                        
        //                 $transfer['transfer'] = 1;                                      // 移交

        //                 $chuli['status'] = array('in','1,2,3,4,5,6,7,9,10,11,12');        // 处理
        //                 // $biaoshi = 1;
        //                 break;
        //             case 22:
        //                 $map_cs['check2_aid']  = $value1['id'];                         //复审认领
        //                 $apply_time_claim      = "second_claim_date";                   //复审认领时间

        //                 $mapx_status['status'] = 7;                                     //复审驳回
        //                 $mapt_status['status'] = array('in','2,3,4,5,9,10,11,12');      //复审通过
        //                 $opreat_time_key       = "check2_time";                         //复审处理时间

        //                 $transfer['transfer'] = 1;                                      // 移交

        //                 $chuli['status'] = array('in','2,3,4,5,9,7,10,11,12');          // 处理
                        
        //                 // $biaoshi = 1;
        //                 break;                    
        //             case 23:
        //                 $map_cs['merchandiser']  = $value1['id'];                         //M1认领
        //                 $apply_time_claim      = "third_claim_date";                      //M1认领时间
                        
        //                 $mapx_status['status'] = 10;                                       //M1驳回
        //                 $mapt_status['status'] = array('in','3,4,5,9,11,12');             //M1通过
        //                 $opreat_time_key       = "monepassdate";                           //M1处理时间

        //                 $transfer['transfer'] = 1;                                         // 移交

        //                 $chuli['status'] = array('in','3,4,5,9,10,11,12');                 // 处理
        //                 break;  
        //             case 24:
        //                 $map_cs['treasurer']   = $value1['id'];                            //财务员id
        //                 $apply_time_claim      = "myloan_claim_date";                      //caiwu认领时间

        //                 $mapx_status['status'] = 11;                                        //财务驳回
        //                 $mapt_status['status'] = array('in','3,4,5,12');                    //财务通过
        //                 $opreat_time_key       = "fk_time";                                 //财务处理时间    

        //                 $chuli['status'] = array('in','3,4,5,11,12');                      // 财务处理     
        //                 // $biaoshi = 1;  
        //                 break;
        //             case 25:
        //                 $map_cs['merchandiser2']   = $value1['id'];                            //M2跟单员id
        //                 $apply_time_claim      = "fourth_claim_date";                          //M2认领时间
         
        //                 $opreat_time_key = "fk_time";
        //                 break;   
        //             case 27:
                        
        //                 $ele_time = array('BETWEEN',[$valuedate,$valuedateend]);
        //                 $map_ele_time['ele_time'] = $ele_time;
        //                 $list[$key]['username'][$key1]['countseq'] = Db::name('user_info')->where(['ele_aid'=>$value1['id']])
        //                                                                                   ->where($map_ele_time)
        //                                                                                   ->where('id > 0')
        //                                                                                   ->count();
                      
        //                 $list[$key]['username'][$key1]['count'] = Db::name('user_info')->where(['ele_aid'=>$value1['id']])
        //                                                                                   ->where($map_ele_time)
        //                                                                                   ->where('ele_status !=0')
        //                                                                                   ->where('id > 0')
        //                                                                                   ->count();
        //                $list[$key]['username'][$key1]['pass'] =  Db::name('user_info')->where(['ele_aid'=>$value1['id']])
        //                                                                                 ->where('id > 0')
        //                                                                                 ->where($map_ele_time)
        //                                                                                 ->where('ele_status = 1')
        //                                                                                 ->count(); //处理情况;
        //                $list[$key]['username'][$key1]['refuse'] = Db::name('user_info')->where(['ele_aid'=>$value1['id']])
        //                                                                                 ->where('id > 0')
        //                                                                                 ->where($map_ele_time)
        //                                                                                 ->where('ele_status = 2')
        //                                                                                 ->count(); //处理情况    
        //                 $biaoji = 1;
        //                 break;                                                       
        //             default:
                       
        //                 break;
        //         } 

                  
        //                 // $list[$key]['username'][$key1]['renl'] = Db::name('apply')->where($apply_time_claim,'>=',$valuedate)
        //                 //                                                            ->where($apply_time_claim,'<',$valuedateend)
        //                 //                                                            ->count();
        //       if($biaoji!=1){

        //                 #未到期
        //                 $list[$key]['username'][$key1]['weidaoqi'] = Db::name('apply')->where('id > 0')
        //                                                                             ->where($map_cs)
        //                                                                             ->where('fk_time','>=',$valuedate)
        //                                                                             ->where('fk_time','<',$valuedateend)
        //                                                                             ->where('status = 3')

        //                                                                            ->count();
        //                 // #按时还款
        //                 // $list[$key]['username'][$key1]['anshi'] = Db::name('apply')->where('id > 0')
        //                 //                                                            ->where($map_cs)
        //                 //                                                            ->where('fk_time','>=',$valuedate)
        //                 //                                                            ->where('fk_time','<',$valuedateend)
        //                 //                                                            ->where('hk_time <= appoint_time')
        //                 //                                                            ->where('status = 5')
        //                 //                                                            ->count();

        //                 #总认领
        //                 $list[$key]['username'][$key1]['countseq'] = Db::name('apply')->where('id > 0')
        //                                                                            ->where($map_cs)
        //                                                                            ->where($apply_time_claim,'>=',$valuedate)
        //                                                                            ->where($apply_time_claim,'<',$valuedateend)
        //                                                                            ->count();

                        
        //                 #  总处理数
        //                 $list[$key]['username'][$key1]['count'] = Db::name('apply')->where('id > 0')
        //                                                                             ->where($map_cs)
        //                                                                            ->where($opreat_time_key,'>=',$valuedate)
        //                                                                            ->where($opreat_time_key,'<',$valuedateend)
        //                                                                            ->where($chuli)
        //                                                                            ->count();
                       
        //                 #  放款 统计     
        //                 $statusfk['status'] = array('in','3,4,5,12');                                                             
        //                 $list[$key]['username'][$key1]['finc_sure_count'] = Db::name('apply')->where('id > 0')
        //                                                                                 ->where($map_cs)
        //                                                                                 ->where($statusfk)
        //                                                                                 ->where('fk_time','>=',$valuedate)
        //                                                                                 ->where('fk_time','<',$valuedateend)
        //                                                                                 ->count();

        //                 #  延期 统计     
        //                 $statusfk['status'] = 12;                                                             
        //                 $list[$key]['username'][$key1]['finc_sure_yanqi_count'] = Db::name('apply')->where('id > 0')
        //                                                                                 ->where($map_cs)
        //                                                                                 ->where($statusfk)
        //                                                                                 ->where('fk_time','>=',$valuedate)
        //                                                                                 ->where('fk_time','<',$valuedateend)
        //                                                                                 ->count();  

        //                 #驳回 统计
        //                 $list[$key]['username'][$key1]['refuse'] = Db::name('apply')->where('id > 0')
        //                                                                                 ->where($map_cs)
        //                                                                                 ->where($mapx_status)
        //                                                                                 ->where($opreat_time_key,'>=',$valuedate)
        //                                                                                 ->where($opreat_time_key,'<',$valuedateend)
        //                                                                                 ->count(); //处理情况    
        //                 #回款 统计
        //                 $list[$key]['username'][$key1]['Repayment'] = Db::name('apply')->where('id > 0')
        //                                                                                 ->where($map_cs)
        //                                                                                 ->where("status = 5")
        //                                                                                 ->where('fk_time','>=',$valuedate)
        //                                                                                 ->where('fk_time','<',$valuedateend)
        //                                                                                 ->count(); //处理情况  
        //                  #按时回款 统计
        //                 $list[$key]['username'][$key1]['Repayment_anshi'] = Db::name('apply')->where('id > 0')
        //                                                                                 ->where($map_cs)
        //                                                                                 ->where("status = 5")
        //                                                                                 ->where('fk_time','>=',$valuedate)
        //                                                                                 ->where('fk_time','<',$valuedateend)
        //                                                                                 ->where('hk_time <= appoint_time')
        //                                                                                 ->count(); //处理情况  
        //                 //  #超时回款 统计
        //                 // $list[$key]['username'][$key1]['Repayment_chaoshi'] = Db::name('apply')->where($map_cs)
        //                 //                                                                 ->where("status = 5")
        //                 //                                                                 ->where('fk_time','>=',$valuedate)
        //                 //                                                                 ->where('fk_time','<',$valuedateend)
        //                 //                                                                 ->where('overdue_count > 0')
        //                 //                                                                 ->count(); //处理情况  

        //                 # 通过 统计                              
        //                 $list[$key]['username'][$key1]['pass'] =  Db::name('apply')->where($map_cs)
        //                                                                                 ->where($mapt_status)
        //                                                                                 ->where($opreat_time_key,'>=',$valuedate)
        //                                                                                 ->where($opreat_time_key,'<',$valuedateend)
        //                                                                                 ->count(); //处理情况;

        //                  # 移交 统计                              
        //                 $list[$key]['username'][$key1]['move'] =  Db::name('apply')->where($map_cs)
        //                                                                                 ->where($transfer)
        //                                                                                 ->where('m1_yijiao_date','>=',$valuedate)
        //                                                                                 ->where('m1_yijiao_date','<',$valuedateend)
        //                                                                                 ->count(); //处理情况;
        //               $list[$key]['username'][$key1]['pass']=$list[$key]['username'][$key1]['pass']>0?$list[$key]['username'][$key1]['pass']:0;                                                                   
                    
        //             // 总还款率
        //             if(($list[$key]['username'][$key1]['finc_sure_count']-$list[$key]['username'][$key1]['weidaoqi']) == 0){
        //                $list[$key]['username'][$key1]['hk_rate'] = '0';
        //             }else{
        //                $list[$key]['username'][$key1]['hk_rate'] = 
        //                substr((($list[$key]['username'][$key1]['Repayment'])/($list[$key]['username'][$key1]['finc_sure_count']-$list[$key]['username'][$key1]['weidaoqi']))*100,0,5);
        //             }

        //             /**
        //              * 部分还款率
        //              */
        //             if(($list[$key]['username'][$key1]['finc_sure_count']-$list[$key]['username'][$key1]['weidaoqi']) == 0){
        //                $list[$key]['username'][$key1]['bf_rate'] = '0';
        //             }else{
        //                $list[$key]['username'][$key1]['bf_rate'] = 
        //                substr((($list[$key]['username'][$key1]['Repayment']+$list[$key]['username'][$key1]['finc_sure_yanqi_count'])/($list[$key]['username'][$key1]['finc_sure_count']-$list[$key]['username'][$key1]['weidaoqi']))*100,0,5);
        //             }

        //             // if($list[$key]['username'][$key1]['Repayment'] == 0){
        //             //    $list[$key]['username'][$key1]['hk_anshi_rate'] = '0';
        //             //    $list[$key]['username'][$key1]['hk_chaoshi_rate'] = '0';
        //             // }else{
        //             //    $list[$key]['username'][$key1]['hk_anshi_rate'] = 
        //             //    substr(($list[$key]['username'][$key1]['Repayment_anshi']/$list[$key]['username'][$key1]['Repayment'])*100,0,5);

        //             //    $list[$key]['username'][$key1]['hk_chaoshi_rate'] = 
        //             //    substr(($list[$key]['username'][$key1]['Repayment_chaoshi']/$list[$key]['username'][$key1]['Repayment'])*100,0,5);
        //             // }

        //             if($list[$key]['username'][$key1]['count'] == 0){
        //                $list[$key]['username'][$key1]['tg_rate'] = '0';
        //             }else{
        //                $list[$key]['username'][$key1]['tg_rate'] = 
        //                substr(($list[$key]['username'][$key1]['pass']/$list[$key]['username'][$key1]['count'])*100,0,5);
        //             }
        //      }       

        //     }


        // } 
        // $data['list'] = $list;
        // return $data;
   }






























    /**
     * [getDataById 根据主键获取详情]
     * @linchuangbin
     * @DateTime  2017-02-10T21:16:34+0800
     * @param     string                   $id [主键]
     * @return    [array]
     */
    public function getDataById($id = '')
    {
        $data=$this->alias('a')
            ->join('qh_user b','a.uid = b.uid')
            ->field('b.username,b.phone,b.status,a.name,a.age,a.sex,a.qq,a.zhima,a.province')
            ->where('a.id',$id)
            ->find();
        return $data;
    }
    /**
     * [updateDataById 编辑]
     * @linchuangbin
     * @DateTime  2017-02-10T21:24:49+0800
     * @param     [type]                   $param [description]
     * @param     [type]                   $id    [description]
     * @return    [type]                          [description]
     */
    public function updateDataById($param, $id)
    {
        $checkData = $this->get($id);
        if (!$checkData) {
            $this->error = '暂无此数据';
            return false;
        }

        // 验证
/*        $validate = validate($this->name);
        if (!$validate->check($param)) {
            $this->error = $validate->getError();
            return false;
        }*/

        try {
            $this->allowField(true)->save($param, [$this->getPk() => $id]);
            return true;
        } catch(\Exception $e) {
            $this->error = '编辑失败';
            return false;
        }
    }


    public function getName($uid)
    {
        return $this->where('uid',$uid)->value("name");
    }
    public function getPhone($uid)
    {
        return $this->where('uid',$uid)->value("phone");
    }    
    public function resetDataById($id)
    {
        // 不能操作超级管理员
        if ($id == 1) {
            $this->error = '非法操作';
            return false;
        }
        $checkData = $this->where('uid',$id)->find();
        if (!$checkData) {
            $this->error = '暂无此数据';
            return false;
        }
        /*if($checkData['status']!=1){
            $this->error = '该账户已被删除或禁用！';
            return false;
        }*/
        $param=array();
        $param['password']=123456;
        $param['password'] = user_md5($param['password'],$auth_key = 'qhdkb');
        $param['last_login_time']=date('Y-m-d H:i:s',time());;
        $res=Db::name('user')->where('uid',$id)->update($param);
        if($res){
            return true;
        }else{
            $this->error = '重置失败';
            return false;
        }
    }
    /**
     * 获取所有子孙
     */
    public function getAllChild($id, &$data = [])
    {
        $map['pid'] = $id;
        $childIds = $this->where($map)->column($this->getPk());
        if (!empty($childIds)) {
            foreach ($childIds as $v) {
                $data[] = $v;
                $this->getAllChild($v, $data);
            }
        }
        return $data;
    }
    public function enableData($ids = [], $status = 1, $delSon = false)
    {
        if (empty($ids)) {
            $this->error = '修改失败';
            return false;
        }

        // 查找所有子元素
        if ($delSon && $status === '0') {
            foreach ($ids as $k => $v) {
                $childIds = $this->getAllChild($v);
                $ids = array_merge($ids, $childIds);
            }
            $ids = array_unique($ids);
        }
        $list=$this->where('id','in',$ids)->select();
        $list = modelo2array($list);
        $data['list'] = $list;
        $uids=array();
        foreach($data['list'] as &$v){
            $uids[]=$v['uid'];
        }
        try {
            Db::name('user')->where('uid','in',$uids)->setField('status', $status);
            return true;
        } catch (\Exception $e) {
            $this->error = '操作失败';
            return false;
        }
    }
    
    /**
     * [getInvitDataById 根据主键获取邀请人数详情]
     * @linchuangbin
     * @DateTime  
     * @param     string                   $id [主键]
     * @return    [array]
     */
    public function getInvitDataById($uid,$page, $limit)
    {
        $dataCount = $this->alias('a')
                    ->join('qh_invite_code b','a.uid = b.uid')
                    ->where('puid',$uid)
                    ->count('b.id');
        
        $invitName = $this->where('uid',$uid)->field('name')->find();
        $invitName = json_decode($invitName);
        $list = $this
        ->where(1);
        // 若有分页
        if ($page && $limit) {
            $list = $this->page($page, $limit);
        }
        
        $list=$this->alias('a')
        ->join('qh_invite_code b','a.uid = b.uid')
        ->field('a.name,a.qq,a.phone,a.zhima,b.create_time')
        ->where('b.puid',$uid)
        ->select();
        
        $list = modelo2array($list);
        $data['list'] = $list;
        $data['dataCount'] = $dataCount;
        $data['invitName'] = $invitName;
        return $data;
    }
    //导出处理数据
    public function export(&$data)
    {
        //文件名
        $title = '';
        if($data['title'] === '初审组')
        {
            $title = 'firstTrial';
        }elseif($data['title'] === 'M1跟单组')
        {
            $title = 'M1';
        }elseif($data['title'] === '财务组')
        {
            $title = 'finance';
        }elseif($data['title'] === 'M2跟单组')
        {
            $title = 'M2';
        }elseif($data['title'] === '电销组')
        {
            $title = 'telemarketing';
        }elseif($data['title'] === '人事经理')
        {
            $title = 'HR';
        }elseif($data['title'] === '推广管理')
        {
            $title = 'generalizationManagement';
        }elseif($data['title'] === '组长')
        {
            $title = 'groupLeader';
        }

        if($data['group_id'] == 27)
        {
            $arrdata[] = [
                '导出时间:'.date('Y-m-d H:i:s'),
                '总认领人数:'.$data['claim'],
                '总处理人数:'.$data['dealwith'],
                '总通过人数:'.$data['pass'],
                '总驳回人数:'.$data['turndown'],
                'vip认证:'.$data['vipCount'],
            ];
            $arrdata[] = ['姓名', '认领人数', '完成人数', '放弃人数', '通过率', 'vip申请人数'];
            $keys = ['username', 'claim', 'pass', 'turndown', 'tg_rate', 'vip'];
        }else{
            $arrdata[] = [
                '导出时间:'.date('Y-m-d H:i:s'),
                '按时还款总人数:'.$data['Repayment_anshi'],
                '总认领人数:'.$data['claim'],
                '总处理人数:'.$data['dealwith'],
                '总延期次数:'.$data['finc_sure_yanqi_count'],
                '按时还款总人数:'.$data['Repayment_anshi'],
                '总通过人数:'.$data['pass'],
                '总驳回人数:'.$data['turndown'],
                '未到期人数:'.$data['weidaoqi'],
                '移交总人数:'.$data['move'],
                '逾期还款人数:0' 
            ];
            $arrdata[] = ['姓名', '认领人数', '驳回人数', '通过人数', '放款次数', '延期次数', '通过率', '未到期/按时/逾期(次)', '还款/部分还款(率)', '移交', '确认还款'];
            $keys = ['username', 'claim', 'turndown', 'pass', 'loan', 'finc_sure_yanqi_count', 'tg_rate', 'wl', 'hkbf', 'move', 'repayments'];
        }
        
        $myloans = new Myloans;
        $filter =  $myloans->filterVal($data['user'],$keys);
        $arrdata = array_merge ($arrdata,  $filter );

        //生成文件 并返回url
        return $myloans->create_xls($arrdata, $title.'.xls');
    }

}